import pandas as pd
from db_helper.mysql_database_helper import MySqlConnectionPool
import configparser
import json


def excel_to_json(path, sheet_name):
    # 读取Excel文件
    df = pd.read_excel(path, sheet_name=sheet_name, engine='openpyxl')

    # 将DataFrame转换为JSON格式
    json_data_inner = df.to_json(orient='records', force_ascii=False)
    return json_data_inner


def pool_init(path="./config.ini", section="mysql-209"):
    """
    初始化mysql
    """
    config = configparser.ConfigParser()
    # 读取配置文件
    config.read(path)
    host = config.get(section, 'host')
    port = config.get(section, 'port')
    database = config.get(section, 'database')
    username = config.get(section, 'username')
    password = config.get(section, 'password')
    init_size = int(config.get(section, 'init-size'))
    pool = MySqlConnectionPool(host=host, port=port, username=username, password=password,
                               database_name=database, pool_size=init_size)
    return pool


# 使用函数
file_path = './excel/0-电动船舶数据采集-数据项-20240928.xlsx'
json_data = excel_to_json(file_path, "报警标志AlarmFlag(移除红色)")
# print(json_data)
config_path = './mysql.ini'


# 处理json数据入库
# 数据标识
fields_names = ['报警对象', '二进制位', '数据表示内容', '标识', '紧急程度', '解释说明', '可能原因', '关联参数', '采样依据']
mysql_pool = pool_init(config_path)
current_alarm_partition = None
for data in [item for item in json.loads(json_data) if item[fields_names[3]]
                         is not None and item[fields_names[3]] != "标识"]:
    if data[fields_names[0]] is not None:
        current_alarm_partition = data[fields_names[0]]
    flag_position = data[fields_names[1]]
    description_items = data[fields_names[2]].split('。')
    data_description = description_items[0]
    alarm_status = data[fields_names[3]]
    urgency_level = '' if data[fields_names[4]] is None else data[fields_names[4]]
    # 解释说明
    explanation = '' if data[fields_names[5]] is None else data[fields_names[5]]
    # 可能原因，处理其中换行时不为；或。的段落
    possible_reason = '' if data[fields_names[6]] is None else data[fields_names[6]]
    possible_reason = ''.join([item if item.endswith('；') or item .endswith('。') else f'{item}；'
                               for item in possible_reason.split("\n") if item is not None and item != ''])
    # 关联参数
    relation_param = '' if data[fields_names[7]] is None else data[fields_names[7]]
    relation_param = ''.join([item if item.endswith('；') or item.endswith('。') else f'{item}；'for item in
                              relation_param.split("\n") if item is not None and item != ''])
    # 采样依据
    sampling_basis = '' if data[fields_names[8]] is None else data[fields_names[8]]
    for item in [text for text in description_items[1].split("；") if len(text) > 0]:
        flags = item.split('：')
        flag = flags[0]
        description = flags[1]
        # 拼接信息打印
        desc = f"""报警对象:{current_alarm_partition},二进制位:{flag_position}, 数据表示内容:{data_description}, 标识位:{flag},
        表述:{description}，报警标识:{alarm_status}, 紧急程度:{urgency_level}, 解释说明:{explanation},
        可能原因:{possible_reason}, 关联参数:{relation_param}, 采样依据:{sampling_basis}"""
        print(desc)
        sql = f"""insert into ship_alarm_flag(alarm_partition, alarm_flag, binary_digit, data_description,
        identity_position, identity_desc, urgency_level, explanation,create_by, possible_reason, relation_param,
        sampling_basis) values('{current_alarm_partition}', '{alarm_status}', '{flag_position}', '{data_description}',
        '{flag}','{description}' , '{urgency_level}', '{explanation}', 'admin', '{possible_reason}',
        '{relation_param}', '{sampling_basis}')"""
        mysql_pool.exec_sql(sql)
